﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.OleDb;
using TenderSampleApp.Models;
using System.IO;
using System.Transactions;

namespace TenderSampleApp.Areas.DAK.Models
{
    public class DAKDAL
    {

        #region Calculation of the Auction
        public double GetFactCost(int VATID)
        {
            TenderDBEntities entitty = new TenderDBEntities();
            VAT_AuctionCollection vat = entitty.VAT_AuctionCollection.Where(kva => kva.AuctionID == 1 && kva.VATID == VATID).FirstOrDefault();
            int StockQuantityOfVat = vat.StockQuantity;
            double sumpr = GetPrepaySumAllPhysicalPersonRequest(VATID);
            double sumjr = GetPrepaySumAllJuridicalPersonRequest(VATID);
            double sum = sumpr + sumjr;
            double auctprice = sum / StockQuantityOfVat;

            if (vat.VAT.REG_OWN == false || vat.StockQuantity <= 17 * 10000)
            {
                if (auctprice < vat.RatedCost)
                    auctprice = vat.RatedCost;
            }
            else if (vat.StockQuantity > 17 * 10000)
            {
                if (auctprice < (vat.BeginCost / 4))
                    auctprice = vat.BeginCost / 4;
            }

            return auctprice < 0.01 ? 0.01 : auctprice;
        }



        #region Get Count Of Valid Request
        public int GetCountOfValidPhysicalPersonRequest(int VATID)
        {
            TenderDBEntities entitty = new TenderDBEntities();
            return entitty.PhysicalPersonRequests.Where(pr => pr.VAT_AuctionCollection.VATID == VATID && pr.IsValidPrepay == true).Count();
        }

        public int GetCountOfValidJuridicalPersonRequest(int VATID)
        {
            TenderDBEntities entitty = new TenderDBEntities();
            return entitty.JuridicalPersonRequests.Where(jr => jr.VAT_AuctionCollection.VATID == VATID && jr.IsValidPrepay == true).Count();
        }

        public int GetCountOfValidAmountRequest(int VATID)
        {
            return GetCountOfValidPhysicalPersonRequest(VATID) + GetCountOfValidJuridicalPersonRequest(VATID);
        }
        #endregion

        #region Get Count Of Invalid Request
        public int GetCountOfInvalidPhysicalPersonRequest(int VATID)
        {
            return GetCountOfPhysicalPersonRequest(VATID) - GetCountOfValidPhysicalPersonRequest(VATID);
        }

        public int GetCountOfInvalidJuridicalPersonRequest(int VATID)
        {
            return GetCountOfJuridicalPersonRequest(VATID) - GetCountOfValidJuridicalPersonRequest(VATID);
        }

        public int GetCountOfInvalidAmountRequest(int VATID)
        {
            return GetCountOfInvalidPhysicalPersonRequest(VATID) + GetCountOfInvalidJuridicalPersonRequest(VATID);
        }
        #endregion

        #region Get Count Of Request
        public int GetCountOfPhysicalPersonRequest(int VATID)
        {
            TenderDBEntities entitty = new TenderDBEntities();
            return entitty.PhysicalPersonRequests.Where(pr => pr.VAT_AuctionCollection.VATID == VATID).Count();
        }

        public int GetCountOfJuridicalPersonRequest(int VATID)
        {
            TenderDBEntities entitty = new TenderDBEntities();
            return entitty.JuridicalPersonRequests.Where(jr => jr.VAT_AuctionCollection.VATID == VATID).Count();
        }

        public int GetCountOfAmountOfRequest(int VATID)
        {
            return GetCountOfPhysicalPersonRequest(VATID) + GetCountOfJuridicalPersonRequest(VATID);
        }
        #endregion

        #region Get Sum Of Request
        public double GetSumAllPhysicalPersonRequest(int VATID)
        {
            TenderDBEntities entitty = new TenderDBEntities();

            var res = entitty.PhysicalPersonRequests.Where(pr => pr.VAT_AuctionCollection.VATID == VATID).AsQueryable();
            return res.All(r => r.Summ == null) ? 0
                 : res.Sum(r => r.Summ);
        }

        public double GetSumAllJuridicalPersonRequest(int VATID)
        {
            TenderDBEntities entitty = new TenderDBEntities();
            var res = entitty.JuridicalPersonRequests.Where(jr => jr.VAT_AuctionCollection.VATID == VATID).AsQueryable();
            return res.All(r => r.Summ == null) ? 0
              : res.Sum(r => r.Summ);
        }

        public double GetSumAmountRequest(int VATID)
        {
            return (GetSumAllJuridicalPersonRequest(VATID) + GetSumAllPhysicalPersonRequest(VATID));
        }
        #endregion

        #region Get Prepay Sum Of Request
        public double GetPrepaySumAllPhysicalPersonRequest(int VATID)
        {
            TenderDBEntities entitty = new TenderDBEntities();

            var res = entitty.PhysicalPersonRequests.Where(pr => pr.VAT_AuctionCollection.VATID == VATID && pr.IsValidPrepay == true).AsQueryable();
            return res.All(r => r.PrepaySumm == null) ? 0
                 : res.Sum(r => r.PrepaySumm);
        }

        public double GetPrepaySumAllJuridicalPersonRequest(int VATID)
        {
            TenderDBEntities entitty = new TenderDBEntities();
            var res = entitty.JuridicalPersonRequests.Where(jr => jr.VAT_AuctionCollection.VATID == VATID && jr.IsValidPrepay == true).AsQueryable();
            return res.All(r => r.PrepaySumm == null) ? 0
              : res.Sum(r => r.PrepaySumm);
        }

        public double GetPrepaySumAmountRequest(int VATID)
        {
            return (GetPrepaySumAllJuridicalPersonRequest(VATID) + GetPrepaySumAllPhysicalPersonRequest(VATID));
        }
        #endregion

        #region Get Returned Prepay Sum Of Request

        public double GetReturnedPrepaySumAllPhysicalPersonRequest(int VATID, double sumforstock)
        {
            TenderDBEntities entitty = new TenderDBEntities();
            var res = entitty.PhysicalPersonRequests.Where(pr => pr.VAT_AuctionCollection.VATID == VATID).AsQueryable();
            double prpsum = res.All(r => r.PrepaySumm == null) ? 0
                 : res.Sum(r => r.PrepaySumm);

            return (prpsum - sumforstock);
        }

        public double GetReturnedPrepaySumAllJuridicalPersonRequest(int VATID, double sumforstock)
        {
            TenderDBEntities entitty = new TenderDBEntities();
            var res = entitty.JuridicalPersonRequests.Where(jr => jr.VAT_AuctionCollection.VATID == VATID).AsQueryable();
            double prpsum = res.All(r => r.PrepaySumm == null) ? 0
                 : res.Sum(r => r.PrepaySumm);

            return (prpsum - sumforstock);
        }


        #endregion

        public double GetStockCount(double orderAmount, int TenderId)
        {
            var t = orderAmount / GetFactCost(TenderId);
            return (double)t;
        }

        public bool IsTenderValid(int AuctionID)
        {
            double factCost = GetFactCost(1);
            if (factCost >= 0.01)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region Select Queries
        public List<PhysicalPersonRequest> GetPhysicalRequestOfCurrentAuction()
        {
            TenderDBEntities entitty = new TenderDBEntities();
            return entitty.PhysicalPersonRequests.Where(p => p.VAT_AuctionCollection.AuctionID == 1).ToList();
        }

        public List<JuridicalPersonRequest> GetJuridicalRequestOfCurrentAuction()
        {
            TenderDBEntities entitty = new TenderDBEntities();
            return entitty.JuridicalPersonRequests.Where(p => p.VAT_AuctionCollection.AuctionID == 1).ToList();
        }

        public List<BankFileResultModel> CheckExpenseByBankFile(int bfID)
        {
            TenderDBEntities entitty = new TenderDBEntities();

            List<string> listNumReqFromBank = entitty.BankFileFields.Where(r => r.BankFileID == bfID).Select(r => r.RequestNumber).ToList();
            List<int> listSummFromBank = entitty.BankFileFields.Where(r => r.BankFileID == bfID).Select(r => r.SUMMA).ToList();

            List<BankFileResultModel> lbfrm = new List<BankFileResultModel>();
            for (int i = 0; i < listNumReqFromBank.Count; i++)
            {
                BankFileResultModel resmod = new BankFileResultModel();
                resmod.RequestNumber = listNumReqFromBank[i];
                JuridicalPersonRequest jr = entitty.JuridicalPersonRequests.Where(j => j.RequestNumber == resmod.RequestNumber).FirstOrDefault();

                if (jr != null && !String.IsNullOrEmpty(jr.RequestNumber))
                {

                    jr.PrepaySumm = listSummFromBank[i];
                    resmod.IsNumberValid = true;
                    if (jr.Summ <= Convert.ToDouble(listSummFromBank[i]))
                    {
                        jr.IsValidPrepay = true;
                        resmod.IsSummValid = true;
                    }

                    entitty.SaveChanges();
                }
                else
                {
                    PhysicalPersonRequest pr = entitty.PhysicalPersonRequests.Where(p => p.RequestNumber == resmod.RequestNumber).FirstOrDefault();
                    if (pr != null && !String.IsNullOrEmpty(pr.RequestNumber))
                    {

                        pr.PrepaySumm = listSummFromBank[i];
                        resmod.IsNumberValid = true;
                        if (pr.Summ <= Convert.ToDouble(listSummFromBank[i]))
                        {
                            pr.IsValidPrepay = true;
                            resmod.IsSummValid = true;
                        }

                        entitty.SaveChanges();
                    }
                }
                lbfrm.Add(resmod);
            }

            return lbfrm;
        }

        /// <summary>
        /// Return list Auctions
        /// </summary>
        /// <returns></returns>
        public List<Auction> GetAuctions()
        {
            TenderDBEntities entitty = new TenderDBEntities();
            return entitty.Auctions.ToList();
        }
        public VAT_AuctionCollection GetVAT_AuctionCollection(int vatID,int auctionID)
        {
            TenderDBEntities entitty = new TenderDBEntities();
            return entitty.VAT_AuctionCollection.Where(vac => vac.AuctionID == auctionID && vac.VATID == vatID).FirstOrDefault();
        }
        #endregion

        #region Insert Queries


        public void AddAuction(Auction auc)
        {
            TenderDBEntities entitty = new TenderDBEntities();
            entitty.AddToAuctions(auc);
            entitty.SaveChanges();
        }
        #endregion
        #region ExcelConnection
        static OleDbConnection oledbConn = null;

        private static OleDbConnection ExcelInstance(string filepath)
        {
            if (oledbConn == null)
            {
                string pathTodirectory = Path.GetDirectoryName(filepath);
                oledbConn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + pathTodirectory + "; Extended Properties=dBASE III;");
            }
            return oledbConn;
        }

        public string ParseRequestNumberOfBankFile(string parsestring)
        {
            string numreq = "";
            foreach (char c in parsestring)
            {
                int result;
                if (int.TryParse(c.ToString(), out result))
                {
                    numreq += result.ToString();
                }
            }
            return numreq;
        }

        //ДОДЕЛАТЬ
        public int ReadDBFFileBank(string filepath)
        {
            TenderDBEntities entitty = new TenderDBEntities();
            BankFile bf = new BankFile();
            OleDbConnection cn = ExcelInstance(filepath);
            using (OleDbCommand cm = cn.CreateCommand())
            {
                string filename = Path.GetFileName(filepath);
                //Insert into BankFile Table

                bf = entitty.BankFiles.Where(b => b.Name == filename).FirstOrDefault();
                if (bf == null)
                {
                    bf = new BankFile();
                    bf.Name = filename;
                    entitty.AddToBankFiles(bf);
                    entitty.SaveChanges();
                }
                else
                {
                    List<BankFileField> listBff = new List<BankFileField>();
                    listBff = entitty.BankFileFields.Where(ff => ff.BankFileID == bf.ID).ToList<BankFileField>();
                    foreach (BankFileField bfitem in listBff)
                    {
                        entitty.BankFileFields.DeleteObject(bfitem);
                    }
                    entitty.SaveChanges();
                }
                //-------------------------//
                cn.Open();
                cm.CommandText = "SELECT * FROM " + filename;
                OleDbDataReader dr = cm.ExecuteReader();
                int t = 0;
                //Insert into BankFileFields Table
                while (dr.Read())
                {
                    BankFileField bff = new BankFileField();
                    bff.ACCCLI = dr["ACCCLI"].ToString();
                    bff.MFOCLI = dr["MFOCLI"].ToString();
                    bff.OKPOCLI = dr["OKPOCLI"].ToString();
                    bff.NAMECLI = dr["NAMECLI"].ToString();
                    bff.BANKCLI = dr["BANKCLI"].ToString();
                    bff.ACCCOR = dr["ACCCOR"].ToString();
                    bff.MFOCOR = dr["MFOCOR"].ToString();
                    bff.OKPOCOR = dr["OKPOCOR"].ToString();
                    bff.NAMECOR = dr["NAMECOR"].ToString();
                    bff.BANKCOR = dr["BANKCOR"].ToString();
                    bff.NDOC = dr["NDOC"].ToString();
                    bff.SUMMA = Convert.ToInt32(dr["SUMMA"]);
                    bff.NAZN = dr["NAZN"].ToString();
                    bff.DT = dr["DT"].ToString();
                    bff.DK = dr["DK"].ToString();
                    bff.TDOC = dr["TDOC"].ToString();
                    bff.DV = dr["DV"].ToString();
                    bff.IMPFLG = dr["IMPFLG"].ToString();
                    bff.BankFileID = bf.ID;
                    if (t == 2)
                        bff.RequestNumber = "00000222000002";
                    else if (t == 3)
                        bff.RequestNumber = "00000222000003";
                    else
                        bff.RequestNumber = ParseRequestNumberOfBankFile(bff.NAZN);

                    entitty.AddToBankFileFields(bff);
                    t++;
                    entitty.SaveChanges();
                }
                //------------------------------------//
                cn.Close();
            }
            return bf.ID;
        }

        public List<VATModel> ReadDBFFileVAT(string filepath)
        {
            OleDbConnection cn = ExcelInstance(filepath);
            using (OleDbCommand cm = cn.CreateCommand())
            {
                string filename = Path.GetFileName(filepath);
                cn.Open();
                cm.CommandText = "SELECT * FROM " + filename;
                OleDbDataReader dr = cm.ExecuteReader();


                List<VATModel> listVAT = new List<VATModel>();

                while (dr.Read())
                {
                    string managerFIO = dr["MANAGER"].ToString();
                    if (managerFIO.IndexOf("КIШУК АНАТОЛIЙ СЕРГIЙОВИЧ") != -1)
                        managerFIO = "КIШУК АНАТОЛIЙ СЕРГIЙОВИЧ";
                    string fullAddress = dr["ADDRESS1"].ToString() + dr["ADDRESS2"].ToString();
                    if (fullAddress.IndexOf("М.ЛУГАНСЬК, ВУЛ.ФРУНЗЕ, 119") != -1)
                        fullAddress = "М.ЛУГАНСЬК, ВУЛ.ФРУНЗЕ, 119";
                    listVAT.Add(new VATModel()
                    {
                        Code = dr["ENT_CODE"].ToString(),
                        BranchCode = dr["BRANCH"].ToString(),
                        Name = dr["ENT_NAME"].ToString().Replace("Ў", "І"),
                        PostIndex = dr["INDEX"].ToString(),
                        FullAddressDBF = fullAddress,
                        PhoneNumber = dr["PHONE"].ToString(),
                        ManagerFIO = managerFIO,
                        TotalAssets = Convert.ToDouble(dr["CAPITAL"]),
                        Region = dr["OBL_CODE"].ToString(),
                        FORMVIP = dr["FORMVIP"].ToString(),
                        MIN_CODE = dr["MIN_CODE"].ToString(),
                        NUMEMPL = dr["NUMEMPL"].ToString(),
                        REG_OWN = Convert.ToBoolean(dr["REG_OWN"]),
                        REGISTRAT = dr["REGISTRAT"].ToString(),
                        TYPEPRIV = dr["TYPEPRIV"].ToString()
                    });
                }
                //------------------------------------//
                cn.Close();
                return listVAT;

            }
        }

        public void LoadEpdtecVatToAuction(string filepath, Auction getAuc)
        {
            TenderDBEntities entitty = new TenderDBEntities();
            OleDbConnection cn = ExcelInstance(filepath);
            int count = 0;
                using (OleDbCommand cm = cn.CreateCommand())
                {
                    
                    string filename = Path.GetFileName(filepath);
                    try
                    {
                        cn.Open();
                    }
                    catch
                    {
                        cn.Close();
                        return;
                    }
                    cm.CommandText = "SELECT * FROM " + filename;
                    string aucnumber = cm.ExecuteScalar().ToString();
                    Auction auc = entitty.Auctions.Where(a => a.Number == aucnumber).FirstOrDefault();
                    if (auc != null)
                    {
                        auc.DateStart = getAuc.DateStart;
                        auc.DateEnd = getAuc.DateEnd;
                    }
                    else
                    {
                        getAuc.Number = aucnumber;
                        auc = getAuc;
                        AddAuction(getAuc);
                    }
                    entitty.SaveChanges();

                    OleDbDataReader dr = cm.ExecuteReader();
                    List<VAT_AuctionCollection> listAucVAT = new List<VAT_AuctionCollection>();

                    while (dr.Read())
                    {
                 
                        string vatcode = dr["ENT_CODE"].ToString();
                        int id = new DAL().GetVATIDByCode(vatcode);
                        if (id == 0)
                            continue;
                        VAT_AuctionCollection vauc = GetVAT_AuctionCollection(id, auc.ID);
                        if (vauc == null)
                            vauc = new VAT_AuctionCollection();

                        vauc.VATID = id;
                        vauc.AuctionID = auc.ID;


                        vauc.RatedCost = Convert.ToDouble(dr["SHAREPRICE"]);
                        vauc.BeginCost = Convert.ToDouble(dr["DEF_PRICE"]);
                        vauc.StockQuantity = Convert.ToInt32(dr["SHARENUM"]);
                        if (vauc.ID == 0)
                            entitty.AddToVAT_AuctionCollection(vauc);
                        count++;
                        entitty.SaveChanges();
                    }
                //------------------------------------//
                   
                cn.Close();

            }
        }
        public void ReadDBFFileBrach(string filepath)
        {
            TenderDBEntities entitty = new TenderDBEntities();
            OleDbConnection cn = ExcelInstance(filepath);
            using (OleDbCommand cm = cn.CreateCommand())
            {
                string filename = Path.GetFileName(filepath);

                cn.Open();
                cm.CommandText = "SELECT * FROM " + filename;
                OleDbDataReader dr = cm.ExecuteReader();
                //Insert into BankFileFields Table
                int id = 1;
                while (dr.Read())
                {
                    Branch branch = new Branch();
                    branch.Code = dr["BR_CODE"].ToString();
                    branch.Name = dr["NAME"].ToString();
                    entitty.AddToBranches(branch);
                    entitty.SaveChanges();
                    id++;
                }
                //------------------------------------//
                cn.Close();

            }
        }
        #endregion
    }
}